/*
 * Copyright 2008 Carnegie Mellon University
 * Licensed under the Apache License, Version 2.0 (the "License"); 
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *  
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, 
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied. See the License for the
 * specific language governing permissions and limitations
 * under the License.
 */
package edu.cmu.lti.sepia.data.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import edu.cmu.lti.sepia.infra.eval.model.Pool;

/**
 * 
 * Manages Pool object in a DB.
 * 
 * @see Pool
 * @author Hideki Shima
 *
 */
public class PoolDAO {

  public static List<Pool> getPools( SQL sql, String table, boolean irPools ) throws SQLException {
    String query = Query.generate( irPools?Query.GET_IR_POOLS:Query.GET_QA_POOLS, table );
    PreparedStatement ps = sql.getConnection().prepareStatement(query);
    ResultSet rs = null;
    List<Pool> pools = new ArrayList<Pool>();
    
    try {
      rs = sql.executeSelect(ps);
      if ( rs == null ) System.out.println( ps );
      while ( rs.next() ) pools.add( rsToObject(rs) );
    } catch (NullPointerException e) {
      
    } finally { 
      if (rs!=null) rs.close();
      if (ps!=null) ps.close();
    }

    return pools;
  }

  //returns inserted pool id 
  public synchronized static Pool addPool( SQL sql, String table, String poolName, boolean irPools ) 
  throws SQLException {
    String query = Query.generate( irPools?Query.ADD_IR_POOL:Query.ADD_QA_POOL, table );
    PreparedStatement ps = sql.getConnection().prepareStatement(query);
    ps.setString(1, poolName);
    sql.execute(ps);
    ps.close();
    Pool pool = getMostRecentlyAddedPool( sql, table, irPools );
    return pool;
  }

  /**
   * Deletes pool entry in the ir_pool & qa_pool tables.
   * Just in case a pool is wrongly deleted, the rows in qa_judgment and ir_judgment   
   * tables with the corresponding pool id are not deleted.
   *     
   * @param sql
   * @param table
   * @param poolName
   * @param irPools
   * @return
   * @throws SQLException
   */
  public synchronized static void deletePool( SQL sql, String table, int poolId, boolean irPools ) 
          throws SQLException {
    String query;
    if (irPools) {
      IRJudgmentUnitDAO.deleteUnits(sql, table, poolId);
      query = Query.generate( Query.DELETE_IR_POOL, table );
    } else {
      QAJudgmentUnitDAO.deleteUnits(sql, table, poolId);
      query = Query.generate( Query.DELETE_QA_POOL, table );
    }
    PreparedStatement ps = sql.getConnection().prepareStatement(query);
    ps.setInt(1, poolId);
    sql.execute(ps);
    ps.close();
  }
  
  public static Pool getMostRecentlyAddedPool( SQL sql, String table, boolean irPools ) 
  throws SQLException {
   String query = Query.generate( irPools?Query.FIND_LATEST_IR_POOL:Query.FIND_LATEST_QA_POOL, table );
    
    PreparedStatement ps = sql.getConnection().prepareStatement(query);
    ResultSet rs = null;
    Pool pool = null;
    
    rs = sql.executeSelect(ps);
    if ( rs == null ) System.out.println( ps );
    if ( rs.next() ) pool = rsToObject(rs);

    rs.close();
    ps.close();
    
    return pool;
  }
  
  private static Pool rsToObject( ResultSet rs ) {
    try {
      Pool pool = new Pool( rs.getInt(1), rs.getString(2) );
      return pool;
    } catch ( Exception e ) {
      e.printStackTrace();
      return null;
    }
  }
  
  public static JSONObject getPoolJson( List<Pool> pools ) throws JSONException {
    JSONObject json = new JSONObject();
    JSONArray jsonArray = new JSONArray();
    for (Pool pool : pools) {
      JSONObject j = new JSONObject();
      j.put("pool_id", pool.getPoolId());
      j.put("pool_name", pool.getPoolName());
      jsonArray.put(j);
    }
    json.put("pools", jsonArray);
    return json;
  }
}
